package com.road_manage.apk;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.Cursor;
import android.database.SQLException;
import android.webkit.JavascriptInterface;
import org.json.JSONObject;
import org.json.JSONArray;
import org.json.JSONException;
import java.util.*;
import com.google.gson.Gson;
import com.google.gson.JsonElement;
import com.google.gson.JsonParser;

public class RoadDataInterface extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "road_data.db";
    private static final int DATABASE_VERSION = 1;
    
    private static final String TABLE_NAME = "road_data";
    
    // 数据库字段集合
    private static final Set<String> ROAD_KEY_SET = new HashSet<>(Arrays.asList(
        "road_id", "road_part_id", "is_work", "road_name", "road_country_id", 
        "road_class", "road_lv", "respon_department", "clean_department", 
        "fix_department", "road_unit", "direction", "start_pile", "end_pile",
        "road_ad_name", "road_ad_code", "road_face_type", "road_build_time",
        "road_fix_time", "road_long", "road_face_width", "road_base_width",
        "road_face_thickness", "road_speed", "road_lane_count", "road_face_area",
        "distance", "check_point", "path_point", "route", "road_desc", "coord_type"
    ));

    public RoadDataInterface(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
            "road_id TEXT, " +
            "road_part_id TEXT PRIMARY KEY, " +
            "is_work TEXT, " +
            "road_name TEXT, " +
            "road_country_id TEXT, " +
            "road_class TEXT, " +
            "road_lv TEXT, " +
            "respon_department TEXT, " +
            "clean_department TEXT, " +
            "fix_department TEXT, " +
            "road_unit TEXT, " +
            "direction TEXT, " +
            "start_pile REAL, " +
            "end_pile REAL, " +
            "road_ad_name TEXT, " +
            "road_ad_code TEXT, " +
            "road_face_type TEXT, " +
            "road_build_time REAL, " +
            "road_fix_time REAL, " +
            "road_long REAL, " +
            "road_face_width REAL, " +
            "road_base_width REAL, " +
            "road_face_thickness REAL, " +
            "road_speed REAL, " +
            "road_lane_count REAL, " +
            "road_face_area REAL, " +
            "distance REAL, " +
            "check_point TEXT, " +
            "path_point TEXT, " +
            "route TEXT, " +
            "road_desc TEXT, " +
            "coord_type TEXT" +
            ")";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    @JavascriptInterface
    public String updata_data(String dataArrJson) {
        try {
            JSONArray dataArr = new JSONArray(dataArrJson);
            SQLiteDatabase db = this.getWritableDatabase();
            int updatedCount = 0;
            
            for (int i = 0; i < dataArr.length(); i++) {
                JSONObject data = dataArr.getJSONObject(i);
                
                if (!data.has("road_part_id")) {
                    continue;
                }
                
                List<String> setSqlArr = new ArrayList<>();
                List<String> queryParams = new ArrayList<>();
                
                Iterator<String> keys = data.keys();
                while (keys.hasNext()) {
                    String key = keys.next();
                    if (!key.equals("road_part_id") && ROAD_KEY_SET.contains(key)) {
                        setSqlArr.add(key + " = ?");
                        Object value = data.get(key);
                        queryParams.add(value == JSONObject.NULL ? null : value.toString());
                    }
                }
                
                if (setSqlArr.isEmpty()) {
                    continue;
                }
                
                String setSqlStr = String.join(",", setSqlArr);
                queryParams.add(data.getString("road_part_id"));
                
                String sql = "UPDATE " + TABLE_NAME + " SET " + setSqlStr + " WHERE road_part_id = ?";
                
                db.execSQL(sql, queryParams.toArray());
                
                // 检查是否有行被更新
                Cursor cursor = db.rawQuery("SELECT changes()", null);
                if (cursor.moveToFirst()) {
                    updatedCount += cursor.getInt(0);
                }
                cursor.close();
            }
            
            db.close();
            return createSuccessResponse(updatedCount);
            
        } catch (Exception e) {
            return createErrorResponse(e.getMessage());
        }
    }

    @JavascriptInterface
    public String check_data(String roadPartId) {
        try {
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE road_part_id = ?", 
                                      new String[]{roadPartId});
            
            boolean exists = cursor.moveToFirst();
            cursor.close();
            db.close();
            
            return createSuccessResponse(exists);
            
        } catch (Exception e) {
            return createErrorResponse(e.getMessage());
        }
    }

    @JavascriptInterface
    public String add_data(String geojsonDataJson) {
        try {
            JSONObject geojsonData = new JSONObject(geojsonDataJson);
            JSONArray features = geojsonData.getJSONArray("features");
            SQLiteDatabase db = this.getWritableDatabase();
            int affectedCount = 0;
            
            for (int i = 0; i < features.length(); i++) {
                JSONObject feature = features.getJSONObject(i);
                
                if (!feature.has("properties") || !feature.has("geometry")) {
                    continue;
                }
                
                JSONObject properties = feature.getJSONObject("properties");
                JSONObject geometry = feature.getJSONObject("geometry");
                
                Map<String, Object> data = new HashMap<>();
                
                // 提取属性数据
                Iterator<String> keys = properties.keys();
                while (keys.hasNext()) {
                    String key = keys.next();
                    if (ROAD_KEY_SET.contains(key)) {
                        Object value = properties.get(key);

                        // --- 这是修改后的逻辑块 ---
                        if (key.equals("check_point")) {
                            // 规则: 如果 check_point 为 null，则跳过不存入
                            if (value != JSONObject.NULL) {
                                data.put(key, value.toString());
                            }
                        } else if (key.equals("route")) {
                            // 规则: 如果 route 为 null，则存入 Java null
                            // 否则，存入 value.toString()
                            data.put(key, value == JSONObject.NULL ? null : value.toString());
                        } else {
                            // 其他字段的原始逻辑: 如果是 JSON null，存入 Java null
                            data.put(key, value == JSONObject.NULL ? null : value);
                        }
                        // --- 逻辑块修改结束 ---
                    }
                }
                
                // 添加几何数据
                if (geometry.has("coordinates")) {
                    data.put("path_point", geometry.getJSONArray("coordinates").toString());
                }
                
                if (data.containsKey("road_part_id")) {
                    List<String> keyArr = new ArrayList<>();
                    List<String> placeholders = new ArrayList<>();
                    List<Object> values = new ArrayList<>();
                    
                    for (Map.Entry<String, Object> entry : data.entrySet()) {
                        String key = entry.getKey();
                        if (ROAD_KEY_SET.contains(key)) {
                            keyArr.add(key);
                            placeholders.add("?");
                            values.add(entry.getValue());
                        }
                    }
                    
                    if (!keyArr.isEmpty()) {
                        String keyArrStr = String.join(", ", keyArr);
                        String placeholdersStr = String.join(", ", placeholders);
                        String sql = "INSERT OR REPLACE INTO " + TABLE_NAME + 
                                   " (" + keyArrStr + ") VALUES (" + placeholdersStr + ")";
                        
                        db.execSQL(sql, values.toArray());
                        
                        Cursor cursor = db.rawQuery("SELECT changes()", null);
                        if (cursor.moveToFirst()) {
                            affectedCount += cursor.getInt(0);
                        }
                        cursor.close();
                    }
                }
            }
            
            db.close();
            return createSuccessResponse(affectedCount);
            
        } catch (Exception e) {
            return createErrorResponse(e.getMessage());
        }
    }
    @JavascriptInterface
    public String delete_data(String roadPartIdArrJson) {
        try {
            JSONArray roadPartIdArr = new JSONArray(roadPartIdArrJson);
            SQLiteDatabase db = this.getWritableDatabase();
            
            List<String> placeholders = new ArrayList<>();
            List<String> values = new ArrayList<>();
            
            for (int i = 0; i < roadPartIdArr.length(); i++) {
                placeholders.add("?");
                values.add(roadPartIdArr.getString(i));
            }
            
            String placeholdersStr = String.join(",", placeholders);
            String sql = "DELETE FROM " + TABLE_NAME + " WHERE road_part_id IN (" + placeholdersStr + ")";
            
            db.execSQL(sql, values.toArray());
            
            Cursor cursor = db.rawQuery("SELECT changes()", null);
            int deletedCount = 0;
            if (cursor.moveToFirst()) {
                deletedCount = cursor.getInt(0);
            }
            cursor.close();
            db.close();
            
            return createSuccessResponse(deletedCount);
            
        } catch (Exception e) {
            return createErrorResponse(e.getMessage());
        }
    }

    @JavascriptInterface
    public String get_all_data() {
        try {
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
            
            JSONObject geojson = exportGeoJson(cursor);
            cursor.close();
            db.close();
            
            return createSuccessResponse(geojson);
            
        } catch (Exception e) {
            return createErrorResponse(e.getMessage());
        }
    }

    @JavascriptInterface
    public String get_filte_data(String filterObjJson) {
        try {
            JSONObject filterObj = new JSONObject(filterObjJson);
            SQLiteDatabase db = this.getReadableDatabase();
            
            StringBuilder baseSql = new StringBuilder("SELECT * FROM " + TABLE_NAME + " WHERE 1 = 1");
            List<String> queryParams = new ArrayList<>();
            
            // 处理输入过滤条件
            if (filterObj.has("filte_input_obj")) {
                JSONObject filteInputObj = filterObj.getJSONObject("filte_input_obj");
                Iterator<String> keys = filteInputObj.keys();
                
                while (keys.hasNext()) {
                    String key = keys.next();
                    String value = filteInputObj.getString(key);
                    
                    if (key.equals("road_id") && !value.isEmpty()) {
                        String[] roadIds = value.split("-");
                        if (roadIds.length == 1) {
                            baseSql.append(" AND road_id = ?");
                            queryParams.add(roadIds[0]);
                        } else if (roadIds.length == 3) {
                            baseSql.append(" AND road_part_id = ?");
                            queryParams.add(value);
                        } else if (roadIds.length == 2) {
                            baseSql.append(" AND road_part_id LIKE ?");
                            queryParams.add(value + "-%");
                        }
                    } else if (ROAD_KEY_SET.contains(key) && !value.isEmpty()) {
                        if ("null".equals(value)) {
                            baseSql.append(" AND ").append(key).append(" IS NULL");
                        } else {
                            baseSql.append(" AND ").append(key).append(" = ?");
                            queryParams.add(value);
                        }
                    }
                }
            }
            
            // 处理复选框过滤条件
            if (filterObj.has("filte_checkbox_obj")) {
                JSONObject filteCheckboxObj = filterObj.getJSONObject("filte_checkbox_obj");
                Iterator<String> keys = filteCheckboxObj.keys();
                
                while (keys.hasNext()) {
                    String key = keys.next();
                    if (ROAD_KEY_SET.contains(key)) {
                        JSONArray filteArr = filteCheckboxObj.getJSONArray(key);
                        
                        if (filteArr.length() == 0) {
                            continue;
                        }
                        
                        List<String> spaceArr = new ArrayList<>();
                        boolean nullSql = false;
                        
                        for (int i = 0; i < filteArr.length(); i++) {
                            String item = filteArr.getString(i);
                            if (!item.isEmpty()) {
                                if ("null".equals(item)) {
                                    nullSql = true;
                                } else {
                                    queryParams.add(item);
                                    spaceArr.add("?");
                                }
                            }
                        }
                        
                        if (!spaceArr.isEmpty()) {
                            String spaceArrStr = String.join(",", spaceArr);
                            baseSql.append(" AND ").append(key).append(" IN (").append(spaceArrStr).append(")");
                            
                            if (nullSql) {
                                baseSql.append(" OR ").append(key).append(" IS NULL");
                            }
                        }
                    }
                }
            }
            
            Cursor cursor = db.rawQuery(baseSql.toString(), queryParams.toArray(new String[0]));
            JSONObject geojson = exportGeoJson(cursor);
            cursor.close();
            db.close();
            
            return createSuccessResponse(geojson);
            
        } catch (Exception e) {
            return createErrorResponse(e.getMessage());
        }
    }

	private JSONObject exportGeoJson(Cursor cursor) throws JSONException {
		Gson gson = new Gson();
		
		JSONObject featureCollection = new JSONObject();
		featureCollection.put("type", "FeatureCollection");
		
		JSONArray features = new JSONArray();
		
		while (cursor.moveToNext()) {
			JSONObject feature = new JSONObject();
			feature.put("type", "Feature");
			
			JSONObject properties = new JSONObject();
			JSONObject geometry = new JSONObject();
			geometry.put("type", "LineString");
			
			for (int i = 0; i < cursor.getColumnCount(); i++) {
				String columnName = cursor.getColumnName(i);
				
				if ("path_point".equals(columnName)) {
					// Gson 自动处理 null/"null"/空字符串
					String value = cursor.getString(i);
					Object parsed = parseJsonString(gson, value);
					geometry.put("coordinates", parsed);
					
				} else if ("check_point".equals(columnName) || "route".equals(columnName)) {
					String value = cursor.getString(i);
					Object parsed = parseJsonString(gson, value);
					properties.put(columnName, parsed);
					
				} else {
					// 其他字段按原样处理
					int columnType = cursor.getType(i);
					
					if (cursor.isNull(i)) {
						properties.put(columnName, JSONObject.NULL);
					} else if (columnType == Cursor.FIELD_TYPE_FLOAT) {
						properties.put(columnName, cursor.getDouble(i));
					} else if (columnType == Cursor.FIELD_TYPE_INTEGER) {
						properties.put(columnName, cursor.getLong(i));
					} else {
						properties.put(columnName, cursor.getString(i));
					}
				}
			}
			
			feature.put("properties", properties);
			feature.put("geometry", geometry);
			features.put(feature);
		}
		
		featureCollection.put("features", features);
		return featureCollection;
	}

	// 辅助方法：使用 Gson 解析 JSON 字符串
	private Object parseJsonString(Gson gson, String value) throws JSONException {
		if (value == null || value.isEmpty() || "null".equals(value)) {
			return JSONObject.NULL;
		}
		
		try {
			JsonElement element = JsonParser.parseString(value);
			
			if (element.isJsonArray()) {
				// 转换为 org.json.JSONArray
				return new JSONArray(value);
			} else if (element.isJsonObject()) {
				// 转换为 org.json.JSONObject
				return new JSONObject(value);
			} else if (element.isJsonNull()) {
				return JSONObject.NULL;
			} else {
				// 其他情况（不应该发生）
				return value;
			}
		} catch (Exception e) {
			// 解析失败，返回 null
			return JSONObject.NULL;
		}
	}
    private String createSuccessResponse(Object data) {
        try {
            JSONObject response = new JSONObject();
            response.put("data", data);
            return response.toString();
        } catch (JSONException e) {
            return createErrorResponse("Failed to create response: " + e.getMessage());
        }
    }

    private String createErrorResponse(String error) {
        try {
            JSONObject response = new JSONObject();
            response.put("error", error);
            return response.toString();
        } catch (JSONException e) {
            return "{\"error\":\"Failed to create error response\"}";
        }
    }
}
